/**
 * 课程订单API路由 (使用Bookings表而不是Orders表)
 */
const Router = require('koa-router');
const router = new Router();
const db = require('../../db');
const auth = require('../../middleware/auth');
const dayjs = require('dayjs');

// 设置路由前缀
router.prefix('/api/admin/orders');

// 获取所有订单列表，支持分页和筛选
router.get('/', async (ctx) => {
  try {
    const { page = 1, limit = 10, status, searchTerm, dateFrom, dateTo, teacherId } = ctx.query;
    
    // 必须提供教师ID
    if (!teacherId) {
      ctx.status = 400;
      ctx.body = { message: '必须提供教师ID' };
      return;
    }
    
    // 修改countQuery部分的查询，使用字符串拼接
    let countQuery = `
      SELECT COUNT(*) as count
      FROM bookings b
      LEFT JOIN courses c ON b.course_id = c.id
      LEFT JOIN users u ON b.user_id = u.id
      WHERE c.teacher_id = ${parseInt(teacherId, 10)}
    `;
    
    // 如果有状态筛选
    if (status) {
      if (status === 'paid') {
        countQuery += ` AND (b.status IN ('confirmed', 'completed', 'upcoming') OR b.paid_at IS NOT NULL) AND (b.refund_amount IS NULL)`;
      } else if (status === 'refunded') {
        countQuery += ` AND (b.refund_amount IS NOT NULL)`;
      } else {
        countQuery += ` AND b.status = '${status}'`;
      }
    }
    
    // 添加搜索条件
    if (searchTerm) {
      const searchValue = `%${searchTerm}%`;
      // 检查是否是订单号格式 (ORD开头)
      if (searchTerm.toUpperCase().startsWith('ORD')) {
        // 提取订单号中的数字部分
        const numericPart = searchTerm.substring(3);
        // 尝试将其转换为数字进行搜索
        const bookingId = parseInt(numericPart, 10);
        if (!isNaN(bookingId)) {
          countQuery += ` AND b.id = ${bookingId}`;
        } else {
          countQuery += ` AND (
            c.title LIKE '${searchValue}' OR 
            u.nickname LIKE '${searchValue}' OR
            b.contact_name LIKE '${searchValue}' OR
            b.contact_phone LIKE '${searchValue}'
          )`;
        }
      } else {
        countQuery += ` AND (
          b.id LIKE '${searchValue}' OR 
          c.title LIKE '${searchValue}' OR 
          u.nickname LIKE '${searchValue}' OR
          b.contact_name LIKE '${searchValue}' OR
          b.contact_phone LIKE '${searchValue}'
        )`;
      }
    }
    
    // 添加日期筛选
    if (dateFrom) {
      countQuery += ` AND b.created_at >= '${dateFrom}'`;
    }
    
    if (dateTo) {
      countQuery += ` AND b.created_at <= '${dateTo} 23:59:59'`;
    }
    
    console.log("计数查询:", countQuery);
    
    // 执行计数查询
    const countResult = await db.query(countQuery);
    
    // 主查询 - 带分页，使用字符串拼接
    let finalQuery = `
      SELECT 
        b.id, b.user_id, b.course_id, b.status, b.created_at, b.refund_amount,
        b.payment_method, b.transaction_id, b.paid_at, b.is_rated,
        b.booking_date, b.time_slot, 
        u.nickname as user_name, u.avatar as user_avatar,
        c.title as course_title, c.image_url as course_cover, c.price as amount,
        t.name as teacher_name
      FROM bookings b
      LEFT JOIN users u ON b.user_id = u.id
      LEFT JOIN courses c ON b.course_id = c.id
      LEFT JOIN teachers t ON c.teacher_id = t.id
      WHERE c.teacher_id = ${parseInt(teacherId, 10)}
    `;
    
    // 添加状态筛选
    if (status) {
      if (status === 'paid') {
        finalQuery += ` AND (b.status IN ('confirmed', 'completed', 'upcoming') OR b.paid_at IS NOT NULL) AND (b.refund_amount IS NULL)`;
      } else if (status === 'refunded') {
        finalQuery += ` AND (b.refund_amount IS NOT NULL)`;
      } else {
        finalQuery += ` AND b.status = '${status}'`;
      }
    }
    
    // 添加搜索条件
    if (searchTerm) {
      const searchValue = `%${searchTerm}%`;
      // 检查是否是订单号格式 (ORD开头)
      if (searchTerm.toUpperCase().startsWith('ORD')) {
        // 提取订单号中的数字部分
        const numericPart = searchTerm.substring(3);
        // 尝试将其转换为数字进行搜索
        const bookingId = parseInt(numericPart, 10);
        if (!isNaN(bookingId)) {
          finalQuery += ` AND b.id = ${bookingId}`;
        } else {
          finalQuery += ` AND (
            c.title LIKE '${searchValue}' OR 
            u.nickname LIKE '${searchValue}' OR
            b.contact_name LIKE '${searchValue}' OR
            b.contact_phone LIKE '${searchValue}'
          )`;
        }
      } else {
        finalQuery += ` AND (
          b.id LIKE '${searchValue}' OR 
          c.title LIKE '${searchValue}' OR 
          u.nickname LIKE '${searchValue}' OR
          b.contact_name LIKE '${searchValue}' OR
          b.contact_phone LIKE '${searchValue}'
        )`;
      }
    }
    
    // 添加日期筛选
    if (dateFrom) {
      finalQuery += ` AND b.created_at >= '${dateFrom}'`;
    }
    
    if (dateTo) {
      finalQuery += ` AND b.created_at <= '${dateTo} 23:59:59'`;
    }
    
    // 添加排序和分页，使用字符串拼接
    const limitNum = parseInt(limit, 10);
    const offset = (parseInt(page, 10) - 1) * limitNum;
    finalQuery += ` ORDER BY b.created_at DESC LIMIT ${limitNum} OFFSET ${offset}`;
    
    console.log("查询语句:", finalQuery);
    
    // 执行查询
    const bookings = await db.query(finalQuery);
    
    // 格式化结果
    const formattedBookings = bookings.map(booking => {
      // 转换状态以符合订单页面展示需求
      let orderStatus = booking.status;
      
      // 如果订单不是取消或退款状态，则统一显示为已支付
      if (booking.status === 'cancelled' && booking.refund_amount) {
        orderStatus = 'refunded';
      } else if (booking.status !== 'cancelled' && booking.status !== 'refunded') {
        orderStatus = 'paid';
      }
      
      // 生成订单号
      const orderNo = `ORD${String(booking.id).padStart(6, '0')}`;
      
      // 格式化日期
      let formattedBookingDate = booking.booking_date;
      if (booking.booking_date) {
        formattedBookingDate = dayjs(booking.booking_date).format('YYYY-MM-DD');
      }
      
      return {
        ...booking,
        order_no: orderNo,
        status: orderStatus,
        booking_date: formattedBookingDate,
        created_at: booking.created_at ? booking.created_at : null
      };
    });
    
    ctx.body = {
      data: formattedBookings,
      pagination: {
        total: countResult[0].count,
        current: parseInt(page, 10),
        pageSize: limitNum
      }
    };
  } catch (error) {
    console.error('获取预约列表失败:', error);
    ctx.status = 500;
    ctx.body = { message: '获取预约列表失败', error: error.message };
  }
});

// 获取预约统计数据
router.get('/statistics/summary', async (ctx) => {
  try {
    const { dateFrom, dateTo, teacherId } = ctx.query;
    
    // 必须提供教师ID
    if (!teacherId) {
      ctx.status = 400;
      ctx.body = { message: '必须提供教师ID' };
      return;
    }
    
    let timeFilter = '';
    const queryParams = [];
    
    if (dateFrom && dateTo) {
      timeFilter = 'WHERE b.created_at BETWEEN ? AND ?';
      queryParams.push(dateFrom, dateTo + ' 23:59:59');
    } else if (dateFrom) {
      timeFilter = 'WHERE b.created_at >= ?';
      queryParams.push(dateFrom);
    } else if (dateTo) {
      timeFilter = 'WHERE b.created_at <= ?';
      queryParams.push(dateTo + ' 23:59:59');
    }
    
    // 如果指定了教师ID，添加过滤条件
    if (timeFilter) {
      timeFilter = `${timeFilter} AND c.teacher_id = ?`;
    } else {
      timeFilter = 'WHERE c.teacher_id = ?';
    }
    queryParams.push(parseInt(teacherId, 10));
    
    // 获取总预约数
    const totalBookingsQuery = `
      SELECT COUNT(*) as count
      FROM bookings b
      LEFT JOIN courses c ON b.course_id = c.id
      ${timeFilter}
    `;
    const totalBookings = await db.query(totalBookingsQuery, queryParams);
    
    // 获取已确认预约数
    const confirmedParams = [...queryParams];
    const confirmedFilter = `${timeFilter} AND b.status = 'confirmed'`;
    const confirmedBookingsQuery = `
      SELECT COUNT(*) as count
      FROM bookings b
      LEFT JOIN courses c ON b.course_id = c.id
      ${confirmedFilter}
    `;
    const confirmedBookings = await db.query(confirmedBookingsQuery, confirmedParams);
    
    // 获取已完成预约数
    const completedParams = [...queryParams];
    const completedFilter = `${timeFilter} AND b.status = 'completed'`;
    const completedBookingsQuery = `
      SELECT COUNT(*) as count
      FROM bookings b
      LEFT JOIN courses c ON b.course_id = c.id
      ${completedFilter}
    `;
    const completedBookings = await db.query(completedBookingsQuery, completedParams);
    
    // 获取已取消预约数
    const cancelledParams = [...queryParams];
    const cancelledFilter = `${timeFilter} AND b.status = 'cancelled'`;
    const cancelledBookingsQuery = `
      SELECT COUNT(*) as count
      FROM bookings b
      LEFT JOIN courses c ON b.course_id = c.id
      ${cancelledFilter}
    `;
    const cancelledBookings = await db.query(cancelledBookingsQuery, cancelledParams);
    
    // 获取已支付订单数
    const paidParams = [...queryParams];
    const paidFilter = `${timeFilter} AND b.status NOT IN ('cancelled', 'refunded')`;
    const paidBookingsQuery = `
      SELECT COUNT(*) as count
      FROM bookings b
      LEFT JOIN courses c ON b.course_id = c.id
      ${paidFilter}
    `;
    const paidBookings = await db.query(paidBookingsQuery, paidParams);
    
    // 获取已退款订单数
    const refundedParams = [...queryParams];
    const refundedFilter = `${timeFilter} AND (b.status = 'refunded' OR (b.status = 'cancelled' AND b.refund_amount IS NOT NULL))`;
    const refundedBookingsQuery = `
      SELECT COUNT(*) as count
      FROM bookings b
      LEFT JOIN courses c ON b.course_id = c.id
      ${refundedFilter}
    `;
    const refundedBookings = await db.query(refundedBookingsQuery, refundedParams);
    
    // 计算总销售额 (除了已取消和已退款的订单都算)
    const salesParams = [...queryParams];
    const salesFilter = `${timeFilter} AND b.status NOT IN ('cancelled', 'refunded')`;
    const totalSalesQuery = `
      SELECT SUM(c.price) as total
      FROM bookings b
      LEFT JOIN courses c ON b.course_id = c.id
      ${salesFilter}
    `;
    const totalSales = await db.query(totalSalesQuery, salesParams);
    
    // 计算总退款金额
    const refundsParams = [...queryParams];
    const refundsFilter = `${timeFilter} AND (b.status = 'refunded' OR (b.status = 'cancelled' AND b.refund_amount IS NOT NULL))`;
    const totalRefundsQuery = `
      SELECT SUM(b.refund_amount) as total
      FROM bookings b
      LEFT JOIN courses c ON b.course_id = c.id
      ${refundsFilter}
    `;
    const totalRefunds = await db.query(totalRefundsQuery, refundsParams);
    
    // 计算净收入 (总销售额 - 总退款金额)
    const totalSalesAmount = totalSales[0].total || 0;
    const totalRefundsAmount = totalRefunds[0].total || 0;
    const netIncome = totalSalesAmount - totalRefundsAmount;
    
    ctx.body = {
      data: {
        total_orders: totalBookings[0].count || 0,
        confirmed_bookings: confirmedBookings[0].count || 0,
        completed_bookings: completedBookings[0].count || 0,
        cancelled_bookings: cancelledBookings[0].count || 0,
        paid_orders: paidBookings[0].count || 0,
        refunded_orders: refundedBookings[0].count || 0,
        total_sales: totalSalesAmount,
        total_refunds: totalRefundsAmount,
        net_income: netIncome
      }
    };
  } catch (error) {
    console.error('获取预约统计数据失败:', error);
    ctx.status = 500;
    ctx.body = { message: '获取预约统计数据失败', error: error.message };
  }
});

// 获取销售趋势数据
router.get('/statistics/trend', async (ctx) => {
  try {
    const { period = 'day', dateFrom, dateTo, teacherId } = ctx.query;
    
    // 必须提供教师ID
    if (!teacherId) {
      ctx.status = 400;
      ctx.body = { message: '必须提供教师ID' };
      return;
    }
    
    // 验证时间范围
    if (!dateFrom || !dateTo) {
      ctx.status = 400;
      ctx.body = { message: '必须提供时间范围' };
      return;
    }
    
    // 根据period选择不同的日期分组格式
    let dateFormat;
    switch (period) {
      case 'week':
        dateFormat = '%x-%v'; // 年-周
        break;
      case 'month':
        dateFormat = '%Y-%m'; // 年-月
        break;
      case 'quarter':
        dateFormat = '%Y-Q%c'; // 年-季度
        break;
      case 'year':
        dateFormat = '%Y'; // 年
        break;
      default:
        dateFormat = '%Y-%m-%d'; // 默认按天
        break;
    }
    
    const query = `
      SELECT 
        DATE_FORMAT(b.created_at, '${dateFormat}') as date_group,
        COUNT(b.id) as order_count,
        SUM(CASE WHEN b.status NOT IN ('cancelled', 'refunded') THEN 1 ELSE 0 END) as paid_count,
        SUM(CASE WHEN b.status = 'refunded' OR (b.status = 'cancelled' AND b.refund_amount IS NOT NULL) THEN 1 ELSE 0 END) as refund_count,
        SUM(CASE WHEN b.status NOT IN ('cancelled', 'refunded') THEN c.price ELSE 0 END) as sales_amount,
        SUM(COALESCE(b.refund_amount, 0)) as refund_amount
      FROM bookings b
      LEFT JOIN courses c ON b.course_id = c.id
      WHERE b.created_at BETWEEN ? AND ? AND c.teacher_id = ?
      GROUP BY date_group
      ORDER BY date_group
    `;
    
    const params = [
      dateFrom,
      dateTo + ' 23:59:59',
      parseInt(teacherId, 10)
    ];
    
    const results = await db.query(query, params);
    
    // 如果数据为空，返回空数组
    ctx.body = {
      data: results || []
    };
  } catch (error) {
    console.error('获取销售趋势数据失败:', error);
    ctx.status = 500;
    ctx.body = { message: '获取销售趋势数据失败', error: error.message };
  }
});

// 获取热门课程统计数据
router.get('/statistics/hot-courses', async (ctx) => {
  try {
    const { limit = 10, dateFrom, dateTo, teacherId } = ctx.query;
    
    // 必须提供教师ID
    if (!teacherId) {
      ctx.status = 400;
      ctx.body = { message: '必须提供教师ID' };
      return;
    }
    
    // 构建查询条件
    const conditions = [];
    const queryParams = [];
    
    // 添加时间范围过滤
    if (dateFrom && dateTo) {
      conditions.push('b.created_at BETWEEN ? AND ?');
      queryParams.push(dateFrom, dateTo + ' 23:59:59');
    } else if (dateFrom) {
      conditions.push('b.created_at >= ?');
      queryParams.push(dateFrom);
    } else if (dateTo) {
      conditions.push('b.created_at <= ?');
      queryParams.push(dateTo + ' 23:59:59');
    }
    
    // 添加状态过滤
    conditions.push("b.status IN ('confirmed', 'completed', 'paid')");
    
    // 添加教师ID过滤
    conditions.push('c.teacher_id = ?');
    queryParams.push(parseInt(teacherId, 10));
    
    // 构建WHERE子句
    const whereClause = conditions.length > 0 ? `WHERE ${conditions.join(' AND ')}` : '';
    
    // 确保limit是数字
    const limitNum = parseInt(limit, 10);
    
    // 获取热门课程 - 使用完整的查询字符串，不使用参数化的LIMIT
    const query = `
      SELECT 
        c.id as course_id,
        c.title as course_title,
        c.image_url as course_cover,
        COUNT(b.id) as order_count,
        t.name as teacher_name,
        c.price as course_price,
        SUM(c.price) as total_amount
      FROM bookings b
      LEFT JOIN courses c ON b.course_id = c.id
      LEFT JOIN teachers t ON c.teacher_id = t.id
      ${whereClause}
      GROUP BY c.id
      ORDER BY order_count DESC
      LIMIT ${limitNum}
    `;
    
    const hotCourses = await db.query(query, queryParams);
    
    ctx.body = { data: hotCourses };
  } catch (error) {
    console.error('获取热门课程统计数据失败:', error);
    ctx.status = 500;
    ctx.body = { message: '获取热门课程统计数据失败', error: error.message };
  }
});

// 获取预约详情
router.get('/:id', async (ctx) => {
  try {
    const { id } = ctx.params;
    const { teacherId } = ctx.query;
    
    // 必须提供教师ID
    if (!teacherId) {
      ctx.status = 400;
      ctx.body = { message: '必须提供教师ID' };
      return;
    }
    
    const query = `
      SELECT b.*, u.nickname as user_name, u.avatar as user_avatar, u.phone as user_phone,
        c.title as course_title, c.image_url as course_cover, c.price as course_price,
        t.name as teacher_name, t.avatar as teacher_avatar
      FROM bookings b
      LEFT JOIN users u ON b.user_id = u.id
      LEFT JOIN courses c ON b.course_id = c.id
      LEFT JOIN teachers t ON c.teacher_id = t.id
      WHERE b.id = ? AND c.teacher_id = ?
    `;
    
    const bookings = await db.query(query, [parseInt(id, 10), parseInt(teacherId, 10)]);
    
    if (bookings.length === 0) {
      ctx.status = 404;
      ctx.body = { message: '预约不存在或无权查看此预约' };
      return;
    }
    
    // 格式化结果
    const booking = {
      ...bookings[0],
      amount: bookings[0].course_price, // 添加金额字段
      order_no: `ORD${String(bookings[0].id).padStart(6, '0')}`, // 生成订单号
      created_at: bookings[0].created_at ? dayjs(bookings[0].created_at).format('YYYY-MM-DD HH:mm:ss') : null
    };
    
    // 格式化预约日期为更友好的格式
    if (booking.booking_date) {
      booking.booking_date = dayjs(booking.booking_date).format('YYYY-MM-DD');
    }
    
    // 为订单页面转换状态
    if (booking.status === 'cancelled' && booking.refund_amount) {
      booking.status = 'refunded';
    } else if (booking.refund_amount) {
      // 如果有退款金额，不管什么状态都应该显示为退款
      booking.status = 'refunded';
    } else if (booking.status === 'completed' || booking.status === 'confirmed' || 
              booking.status === 'upcoming' || booking.paid_at) {
      booking.status = 'paid';
    }
    
    // 如果有退款但缺少退款时间，添加默认时间
    if (booking.refund_amount && !booking.refunded_at) {
      booking.refunded_at = booking.created_at;
    }
    
    // 如果缺少支付方式信息，添加默认值
    if (!booking.payment_method && booking.status === 'paid') {
      booking.payment_method = 'wechat';
      booking.paid_at = booking.created_at;
    }
    
    ctx.body = { data: booking };
  } catch (error) {
    console.error('获取预约详情失败:', error);
    ctx.status = 500;
    ctx.body = { message: '获取预约详情失败', error: error.message };
  }
});

// 更新预约状态
router.put('/:id/status', async (ctx) => {
  try {
    const { id } = ctx.params;
    const { status, admin_comments, teacherId } = ctx.request.body;
    
    // 必须提供教师ID
    if (!teacherId) {
      ctx.status = 400;
      ctx.body = { message: '必须提供教师ID' };
      return;
    }
    
    if (!['pending', 'confirmed', 'completed', 'cancelled'].includes(status)) {
      ctx.status = 400;
      ctx.body = { message: '无效的预约状态' };
      return;
    }
    
    // 先验证此预约是否属于该教师的课程
    const verifyQuery = `
      SELECT b.id 
      FROM bookings b
      JOIN courses c ON b.course_id = c.id
      WHERE b.id = ? AND c.teacher_id = ?
    `;
    
    const verifyResult = await db.query(verifyQuery, [parseInt(id, 10), parseInt(teacherId, 10)]);
    
    if (verifyResult.length === 0) {
      ctx.status = 403;
      ctx.body = { message: '无权修改此预约' };
      return;
    }
    
    // 更新预约状态
    const updateQuery = `
      UPDATE bookings 
      SET status = ?, 
          admin_comments = ?
      WHERE id = ?
    `;
    
    await db.query(updateQuery, [
      status, 
      admin_comments,
      parseInt(id, 10)
    ]);
    
    ctx.body = { message: '预约状态已更新' };
  } catch (error) {
    console.error('更新预约状态失败:', error);
    ctx.status = 500;
    ctx.body = { message: '更新预约状态失败', error: error.message };
  }
});

// 更新订单支付状态
router.put('/:id/payment', async (ctx) => {
  try {
    const { id } = ctx.params;
    const { payment_method, transaction_id, paid_at, teacherId } = ctx.request.body;
    
    // 必须提供教师ID
    if (!teacherId) {
      ctx.status = 400;
      ctx.body = { message: '必须提供教师ID' };
      return;
    }
    
    // 验证订单是否属于该教师的课程
    const verifyQuery = `
      SELECT b.id 
      FROM bookings b
      JOIN courses c ON b.course_id = c.id
      WHERE b.id = ? AND c.teacher_id = ?
    `;
    
    const verifyResult = await db.query(verifyQuery, [parseInt(id, 10), parseInt(teacherId, 10)]);
    
    if (verifyResult.length === 0) {
      ctx.status = 403;
      ctx.body = { message: '无权修改此订单的支付状态' };
      return;
    }
    
    // 更新支付状态
    const updateQuery = `
      UPDATE bookings 
      SET payment_method = ?, 
          transaction_id = ?,
          paid_at = ?,
          status = 'paid'
      WHERE id = ?
    `;
    
    await db.query(updateQuery, [
      payment_method,
      transaction_id,
      paid_at ? dayjs(paid_at).format('YYYY-MM-DD HH:mm:ss') : dayjs().format('YYYY-MM-DD HH:mm:ss'),
      parseInt(id, 10)
    ]);
    
    ctx.body = { message: '订单支付状态已更新' };
  } catch (error) {
    console.error('更新订单支付状态失败:', error);
    ctx.status = 500;
    ctx.body = { message: '更新订单支付状态失败', error: error.message };
  }
});

// 处理订单退款
router.post('/:id/refund', async (ctx) => {
  try {
    const { id } = ctx.params;
    const { refund_amount, refund_reason, teacherId } = ctx.request.body;
    
    // 必须提供教师ID
    if (!teacherId) {
      ctx.status = 400;
      ctx.body = { message: '必须提供教师ID' };
      return;
    }
    
    // 验证订单是否属于该教师的课程
    const verifyQuery = `
      SELECT b.id, c.price
      FROM bookings b
      JOIN courses c ON b.course_id = c.id
      WHERE b.id = ? AND c.teacher_id = ?
    `;
    
    const verifyResult = await db.query(verifyQuery, [parseInt(id, 10), parseInt(teacherId, 10)]);
    
    if (verifyResult.length === 0) {
      ctx.status = 403;
      ctx.body = { message: '无权处理此订单的退款' };
      return;
    }
    
    const coursePrice = verifyResult[0].price;
    
    // 验证退款金额
    if (refund_amount > coursePrice) {
      ctx.status = 400;
      ctx.body = { message: '退款金额不能超过课程价格' };
      return;
    }
    
    // 更新退款信息
    const updateQuery = `
      UPDATE bookings 
      SET refund_amount = ?, 
          refund_reason = ?,
          admin_id = ?,
          refunded_at = NOW(),
          status = 'cancelled'
      WHERE id = ?
    `;
    
    await db.query(updateQuery, [
      refund_amount,
      refund_reason,
      parseInt(teacherId, 10),
      parseInt(id, 10)
    ]);
    
    ctx.body = { message: '订单退款已处理，课程状态已设置为已取消' };
  } catch (error) {
    console.error('处理订单退款失败:', error);
    ctx.status = 500;
    ctx.body = { message: '处理订单退款失败', error: error.message };
  }
});

module.exports = router; 